using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data;
using System.Xml;
using System.Configuration;
using System.Data.SqlClient;
 
using System.Data.Common;
namespace DataProvider
{
    public class SqlClient : ISqlClient
    {
        private const int timeOut = 0;
        #region Connetion String
        private String connStr;
        
        public String ConnectionString
        {
            get
            {
                return connStr;
            }
            set
            {
                connStr = value;
            }
        }
        public SqlClient(String connStr)
        {
            this.connStr = connStr;
        }
        public SqlClient()
        {
            if (Config.Items.ContainsKey("ConnectionString"))
            {
                this.connStr = Config.Items["ConnectionString"];
            }
        }

        public bool Test()
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        public DataTable Execute(SqlStatemate sqlStatement)
        {
            try
            {
                DataTable result = new DataTable();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message +" " + sqlStatement.Sql);
                throw e;
            }
        }
        public DataSet ExecuteDataSet(SqlStatemate sqlStatement)
        {
            try
            {
                DataSet result = new DataSet();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }
        
        public DataTable Execute(String sql)
        {
            try
            {
                SqlStatemate sqlState = new SqlStatemate(sql);
                return this.Execute(sqlState);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public DataTable PagelyExecute(SqlStatemate sqlStatement, int page, int itemCountPerPage)
        {
            try
            {
                page = page < 1 ? 1 : page;
                int startItem = itemCountPerPage * (page - 1);

                DataTable result = new DataTable();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                       // cmd.Transaction.IsolationLevel = IsolationLevel.ReadUncommitted;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(startItem, itemCountPerPage, result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public DataSet PagelyExecuteReturnDataSet(SqlStatemate sqlStatement, int page, int itemCountPerPage)
        {
            try
            {
                page = page < 1 ? 1 : page;
                int startItem = itemCountPerPage * (page - 1);

                DataSet result = new DataSet();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        //cmd.Transaction.IsolationLevel = IsolationLevel.ReadUncommitted;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            adapter.Fill(result,startItem, itemCountPerPage, "Product");
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }
       
        public DataTable PagelyExecuteStoreProcedure(String storedProc,
                                                    int page,
                                                    int itemCountPerPage,
                                                    params DbParameter[] parameters
                                                            )
        {
            try
            {
                page = (page == 0 ? 1 : page);
                DataTable resultTable = new DataTable();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(storedProc, conn);
                    cmd.CommandTimeout = timeOut;
                    cmd.CommandType = CommandType.StoredProcedure;

                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        int startItem = itemCountPerPage * (page - 1);
                        if (startItem < 0)
                            startItem = 0;
                        adapter.Fill(startItem, itemCountPerPage, resultTable);
                    }
                }
                return resultTable;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + storedProc);
                throw e;
            }
        }

        public DataTable ExecuteStoreProcedureReturnTable(String storedProc,
                                                      params DbParameter[] parameters)
        {
            try
            {
                DataTable resultTable = new DataTable();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(storedProc, conn);
                    cmd.CommandTimeout = timeOut;
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {

                        adapter.Fill(resultTable);
                    }
                }
                return resultTable;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + storedProc);
                throw e;
            }
        }

        public DataSet ExecuteStoreProcedureReturnDataSet(String storedProc, params DbParameter[] parameters)
        {
            try
            {
                DataSet dataSet = new DataSet();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(storedProc, conn);
                    cmd.CommandTimeout = timeOut;

                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                    {
                        adapter.Fill(dataSet);
                    }
                }
                return dataSet;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + storedProc);
                throw e;
            }
        }

        public void ExecuteStoreProcedureNoQuery(String storedProc, params DbParameter[] parameters)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(storedProc, conn);
                    cmd.CommandTimeout = timeOut;

                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + storedProc);
                throw e;
            }
        }

        public String ExecuteStoreProcedureSingleString(String storedProc, params DbParameter[] parameters)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlCommand cmd = new SqlCommand(storedProc, conn);
                    cmd.CommandTimeout = timeOut;

                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null && parameters.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameters);
                    }
                    ///////////////////////////////////////////////////
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        StringBuilder results = new StringBuilder();
                        do
                        {
                            while (reader.Read())
                            {
                                Object result = reader.GetValue(0);
                                results.Append(result.ToString());
                            }
                        } while (reader.NextResult());
                        return results.ToString();
                    }
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + storedProc);
                throw e;
            }
        }

        public void ExecuteNoResult(String sql)
        {
            try
            {
                SqlStatemate stateMent = new SqlStatemate(sql);
                ExecuteNoResult(stateMent);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }
        
        public void ExecuteNoResult(SqlStatemate sqlStatement)
        {
            try
            {
                if (sqlStatement == null)
                    return;
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public void BatchExecute(IList<SqlStatemate> sqlStatementSet)
        {
            try
            {
                if (sqlStatementSet == null)
                    return;
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    SqlTransaction trans = conn.BeginTransaction();
                    try
                    {
                        foreach (SqlStatemate sqlState in sqlStatementSet)
                        {
                          
                            using (SqlCommand cmd = new SqlCommand(sqlState.Sql, conn))
                            {
                                cmd.Transaction = trans;
                                cmd.CommandTimeout = timeOut;
                                cmd.CommandType = CommandType.Text;

                                if (sqlState.Parameters != null && sqlState.Parameters.Count > 0)
                                {
                                    cmd.Parameters.AddRange(sqlState.Parameters.ToArray());
                                }
                                cmd.ExecuteNonQuery();

                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message  );
                throw e;
            }
        }
       
        public List<String> GetStringList(SqlStatemate sqlStatement)
        {
            List<String> results = new List<string>();
            try
            {
                if (sqlStatement == null)
                    return null;
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            do
                            {
                                while (reader.Read())
                                {
                                    Object result = reader.GetValue(0);
                                    results.Add(result.ToString());
                                }
                            } while (reader.NextResult());
                        }
                    }
                }
                return results;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public String GetSingleResult(SqlStatemate sqlStatement)
        {
            try
            {
                if (sqlStatement == null)
                    return null;

                StringBuilder results = new StringBuilder();
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            do
                            {
                                while (reader.Read())
                                {
                                    Object result = reader.GetValue(0);
                                    results.Append(result.ToString());
                                }
                            } while (reader.NextResult());
                        }
                    }
                }
                return results.ToString();
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public String GetSingleResult(String sql)
        {
            try
            {
                SqlStatemate state = new SqlStatemate(sql);
                return GetSingleResult(state);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public DbDataReader ExecuteReader(SqlStatemate sqlStatement)
        {
            DbCommand returnCmd = null;
            return ExecuteReader(sqlStatement, ref returnCmd);
        }

        public DbDataReader ExecuteReader(SqlStatemate sqlStatement, ref DbCommand returnCmd)
        {
            if (sqlStatement == null)
                return null;
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connStr);
                conn.Open();
                SqlCommand cmd = new SqlCommand(sqlStatement.Sql, conn);
                cmd.CommandTimeout = timeOut;
                cmd.CommandType = CommandType.Text;
                if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                }
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                returnCmd = cmd;
                return reader;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError("Sql Error:" + e.Message + " Sql:" + sqlStatement.Sql);
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
                return null;
            }
        }

        public DbDataReader ExecuteReader(String sql)
        {
            DbCommand returnCmd = null;
            return ExecuteReader(sql,ref returnCmd);
        }

        public DbDataReader ExecuteReader(String sql, ref DbCommand returnCmd)
        {
            try
            {
                SqlStatemate state = new SqlStatemate(sql);
                return ExecuteReader(state, ref returnCmd);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public DbDataReader ExecuteStoreProcedureReader(String storedProc, params DbParameter[] parameters)
        {
            DbCommand returnCmd = null;
            return ExecuteStoreProcedureReader(storedProc, ref returnCmd, parameters);
        }

        public DbDataReader ExecuteStoreProcedureReader(String storedProc, ref DbCommand returnCmd, params DbParameter[] parameters)
        {
            SqlDataReader reader = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(connStr);
                conn.Open();
                SqlCommand cmd = new SqlCommand(storedProc, conn);
                cmd.CommandTimeout = timeOut;

                cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null && parameters.Length > 0)
                {
                    cmd.Parameters.AddRange(parameters);
                }
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                returnCmd = cmd;
                return reader;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError("Sql Error:" + e.Message + " Sql:" + storedProc);
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
                return null;
            }
        }

        public bool ExecuteTransaction(IList<SqlStatemate> sqlStatementSet)
        {
           if (sqlStatementSet == null)
                return false;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction Transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted, "Transaction");
                try
                {
                    foreach (SqlStatemate sqlState in sqlStatementSet)
                    {
                        using (SqlCommand cmd = new SqlCommand(sqlState.Sql, conn))
                        {
                            cmd.CommandTimeout = timeOut;
                            if (sqlState.Parameters != null && sqlState.Parameters.Count > 0)
                            {
                                cmd.Parameters.AddRange(sqlState.Parameters.ToArray());
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                    Transaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    DataLayerLog.LogError(e.Message);
                    Transaction.Rollback();
                    return false;
                }
            }
        }

        public List<String> GetTableList()
        {
            List<String> results = new List<string>();
            String sql =@" SELECT name FROM  sysobjects WHERE  type = 'U'";
            using (DbDataReader reader = ExecuteReader(sql))
            {
                do
                {
                    while (reader.Read())
                    {
                        results.Add(reader.GetString(0));
                    }
                } while (reader.NextResult());
            }
            return results;
        }

        public List<TableFieldInfo> GetFieldInfo(string tableName, params String[] reservedField)
        {
            String sql = @" select  A.name as fieldname ,B.name as  type 
                            from  syscolumns A inner join systypes B ON A.xtype=B.xtype and B.name<>'sysname'
                            where  A.id=object_id('{0}') ";
            sql = String.Format(sql, tableName);
            List<TableFieldInfo> fieldList = new List<TableFieldInfo>();

            List<String> reservedFieldSet = new List<string>();
            if (reservedField != null)
            {
                reservedFieldSet.AddRange(reservedField);
            }
            using (DbDataReader reader = ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    String fieldName = reader.GetString(0);
                    if (reservedFieldSet.Contains(fieldName))
                    {
                        continue;
                    }
                    TableFieldInfo fieldInfo = new TableFieldInfo();
                    fieldInfo.FieldName = fieldName;
                    fieldInfo.FieldType = reader.GetString(1);
                    fieldList.Add(fieldInfo);
                }
            }
            return fieldList;
        }
 
        //////////////////////////////////////////////////////////////////////////////////////////////
        
    }

}
